<?php

class member extends Model
{
	function getOrgMembersAdminByOrgName( $name )
	{
		$gmember = $this->db->getAll("SELECT members.stud_no, 
				user.firstname, 
				user.lastname, 
				user.middlename,
				members.date,
				members.banned,
				organization.name org,
				admins.admin_id
			FROM (members INNER JOIN organization ON members.org_id = organization.org_id 
			INNER JOIN user ON members.stud_no = user.stud_no)  
			LEFT JOIN (SELECT stud_no, admin_id FROM admin INNER JOIN organization oa ON admin.org_id = oa.org_id WHERE oa.name = ?) admins
			ON members.stud_no = admins.stud_no
		WHERE
			organization.name = ?
			ORDER BY user.lastname
			",
			array( $name, $name )	
		);
		return $gmember;
	}


	function getOrgMembersByOrgName( $name )
	{
		$gmember = $this->db->getAll("SELECT members.stud_no, 
				user.firstname, 
				user.lastname, 
				user.middlename,
				members.date,
				members.banned,
				organization.name org
			FROM members,organization, user
			WHERE members.org_id = organization.org_id 
			AND members.stud_no = user.stud_no
			AND user.admin = '0'
			AND organization.name = ?
			ORDER BY user.lastname
			",
			array( $name )	
		);
		return $gmember;
	}

	function getOrgMembers()
	{
		$gmember = $this->db->getAll("SELECT members.stud_no, 
				user.firstname, 
				user.lastname, 
				user.middlename,
				members.date,
				organization.name org
			FROM members,organization, user
			WHERE members.org_id = organization.org_id 
			AND members.stud_no = user.stud_no
			AND organization.org_id = ?",
			array( (int) $id )	
		);
		return $gmember;
	}
	
	function getMember($id)
	{
		$gmember = $this->db->getAll("SELECT members.stud_no, 
				user.firstname, 
				user.lastname, 
				user.middlename,
				members.date,
				organization.name Org
			FROM members,organization, user
			WHERE members.org_id = organization.org_id 
			AND members.stud_no = user.stud_no
			AND members.org_id = ?",
			array( (int) $id )	
		);
		return $gmember;
	}

	function insertMember( $stud_no, $org_id, $date, $banned )
	{
		$ismem = $this->db->getAll("SELECT stud_no
			FROM members WHERE stud_no = ? AND org_id = ?",
			array( $stud_no, (int) $org_id )	
		);
		if( count( $ismem ) === 0 ) {
			$imember = $this->db->execute("INSERT INTO members( stud_no,
				org_id,
				date,
				banned )
				VALUES ( ?, ?, ?, ? )", 
				array( $stud_no, (int) $org_id, $date, $banned )
			);
			return $imember;
		}
	}

	 function updateMember( $org_id, $date,$banned, $stud_no ) 
	{
		$umember = $this->db->execute("UPDATE members
			SET org_id = ?,
				date = ?,
				banned = ?
			WHERE stud_no = ?",
			array( (int) $org_id, $date, $banned, $stud_no )
		);
		return $umember;
	}

	function deleteMember ( $stud_no, $orgid )
	{
		$dmember = $this->db->execute("DELETE FROM members
			WHERE stud_no = ? AND org_id = ?",
			array( $stud_no, (int) $orgid )
		);
	}
	
	function logIn ( $stud_no, $password )
	{
		$login = $this->db->getAll("SELECT members.stud_no, 
				user.firstname, 
				user.lastname, 
				user.middlename,
				members.date,
				organization.name Org
			FROM members, organization, user
			WHERE members.stud_no = '?'
			AND user.password = '?'
			AND members.org_id = organization.org_id 
			AND members.stud_no = user.stud_no",
			array( $stud_no, $password )	
		);
		return $login;
	}
	
	function getMyOrgs ( $stud_no )
	{
		$myorg = $this->db->getAll("SELECT members.stud_no, 
			organization.name, organization.description, organization.logo
			FROM members, organization
			WHERE members.stud_no = ?
			AND members.org_id = organization.org_id ",
		array( $stud_no )
		);
		return $myorg;
	}

	function isMember($studNo, $orgName) {
		$memberships = $this->db->getAll(
			'SELECT m.members_id
				FROM members m
				WHERE m.stud_no = ? AND
				m.org_id = ?',
			array( $studNo, $orgName )
		);
		if( count( $memberships ) > 0 ) return true;
		else return false;
	}
	
	function amIMember($studNo, $orgName) {
		$memberships = $this->db->getAll(
			'SELECT m.members_id
				FROM members m, organization o
				WHERE m.stud_no = ? AND
				o.name = ? AND
				o.org_id = m.org_id',
			array( $studNo, $orgName )
		);
		if( count( $memberships ) > 0 ) return true;
		else return false;
	}

	function amIBanned($studNo, $orgId) {
		$memberships = $this->db->getAll(
			'SELECT m.members_id
				FROM members m
				WHERE m.stud_no = ? 
				AND m.org_id = ?
				AND m.banned = 1
				',
			array( $studNo, $orgId )
		);
		if( count( $memberships ) > 0 ) return true;
		else return false;
	}
	
	function getMyEvent( $stud_no )
	{
		$myevent = $this->db->getAll("SELECT event.name Event,
				event.event_id,
				event.date,
				organization.name Org,
				organization.logo logo
			FROM event,organization,members,registration
			WHERE event.org_id = organization.org_id
			AND registration.stud_no = members.stud_no
			AND event.event_id = registration.event_id
			AND members.org_id = organization.org_id
			AND members.stud_no = ?
			ORDER BY event.date DESC",
			array( $stud_no )
		);
		return $myevent;
	}

	function getMyEventById( $stud_no, $id )
	{
		$myevent = $this->db->getAll("SELECT event.name Event,
				event.date,
				event.event_id,
				event.description,
				event.venue,
				registration.registration_id,
				organization.name org,
				organization.logo logo
			FROM event,organization,members,registration
			WHERE event.org_id = organization.org_id
			AND registration.stud_no = members.stud_no
			AND event.event_id = registration.event_id
			AND members.org_id = organization.org_id
			AND members.stud_no = ?
			AND event.event_id = ?
			ORDER BY event.date DESC",
			array( $stud_no, (int) $id)
		);
		return $myevent;
	}
	
	function updateLastVisit( $stud_no, $org_id ) {
		$this->db->execute(
			"UPDATE members
			SET date = ?
			WHERE stud_no = ?
			AND org_id = ?",
			array( 
				gmdate('Y-m-d H:i:s'),
				$stud_no,
				(int) $org_id				
			)
		);
	}
	
	function ban( $stud_no, $org_id ) {
		$this->db->execute(
			"UPDATE members
			SET banned = ?
			WHERE stud_no = ?
			AND org_id = ?",
			array( 
				1,
				$stud_no,
				(int) $org_id				
			)
		);
	}
	
	function unban( $stud_no, $org_id ) {
		$this->db->execute(
			"UPDATE members
			SET banned = ?
			WHERE stud_no = ?
			AND org_id = ?",
			array( 
				0,
				$stud_no,
				(int) $org_id
			)
		);
	}
	
	function getInactiveMembers($org_id) {
		return $this->db->getAll(
			"SELECT stud_no
			FROM member
			WHERE date < (? - INTERVAL 30 DAY)
			AND org_id = ?",
			array( 
				gmdate('Y-m-d H:i:s'), 
				(int) $org_id 
			)
		);
	}
	
	function getMailAddresses($orgid) {
		$sth = $this->db->dbh()->prepare("SELECT u.email FROM user u INNER JOIN members m ON u.stud_no = m.stud_no WHERE m.org_id = ? AND u.email != ''");
		$sth->execute( array( (int) $orgid ) );
		$sth->setFetchMode(PDO::FETCH_COLUMN, 0	);
		/* Fetch all of the remaining rows in the result set */
		$result = $sth->fetchAll();
		return ( $result );
	}

}